"""
AssessmentPlan CRUD 操作
定义所有与数据库交互的函数，隔离业务代码与SQL语句
"""
from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession
from typing import Optional, List, Dict, Any, Tuple
from uuid import UUID

from models.assessmentPlan import AssessmentPlan
from schemas.assessmentPlan import AssessmentPlanCreate, AssessmentPlanUpdate
from log import log_info

async def get_assessment_plans(
    db: AsyncSession, 
    skip: int = 0,
    limit: int = 100,
    filters: Optional[Dict[str, Any]] = None
) -> Tuple[List[AssessmentPlan], int]:
    """
    获取评估计划列表，支持分页和过滤

    Args:
        db: 数据库会话
        skip: 跳过记录数
        limit: 返回记录限制
        filters: 过滤条件字典

    Returns:
        包含评估计划列表和总记录数的元组
    """
    query = select(AssessmentPlan)
    result = await db.execute(query)
    plans = result.scalars().all()
    
    # 应用过滤条件
    if filters:
        if filters.get("name"):
            plans = [plan for plan in plans if filters['name'] in plan.name]
        if filters.get("status"):
            plans = [plan for plan in plans if plan.status == filters["status"]]
        if filters.get("assessment_type"):
            plans = [plan for plan in plans if plan.assessment_type == filters["assessment_type"]]
    
    # 获取总数
    total_result = await db.execute(select(func.count()).select_from(query.subquery()))
    total = total_result.scalar_one()
    
    # 应用排序和分页
    query = query.order_by(AssessmentPlan.created_at.desc()).offset(skip).limit(limit)
    
    # 执行查询
    result = await db.execute(query)
    items = result.scalars().all()
    
    log_info(f"Retrieved {len(items)} assessment plans out of {total}", module="crud.assessmentPlan")
    return items, total

async def get_assessment_plan_by_id(db: AsyncSession, plan_id: UUID) -> Optional[AssessmentPlan]:
    """
    根据ID获取评估计划

    Args:
        db: 数据库会话
        plan_id: 评估计划ID

    Returns:
        评估计划对象，如未找到则返回None
    """
    query = select(AssessmentPlan).where(AssessmentPlan.id == plan_id)
    result = await db.execute(query)
    plan = result.scalar_one_or_none()
    
    if plan:
        log_info(f"Retrieved assessment plan with ID {plan_id}", module="crud.assessmentPlan")
    else:
        log_info(f"No assessment plan found with ID {plan_id}", module="crud.assessmentPlan")
    
    return plan


async def get_assessment_plan_by_name_and_type(
    db: AsyncSession, 
    name: str, 
    assessment_type: str
) -> Optional[AssessmentPlan]:
    """
    根据名称和评估类型获取评估计划

    Args:
        db: 数据库会话
        name: 评估计划名称
        assessment_type: 评估类型

    Returns:
        评估计划对象，如未找到则返回None
    """
    query = select(AssessmentPlan).where(
        AssessmentPlan.name == name,
        AssessmentPlan.assessment_type == assessment_type
    )
    result = await db.execute(query)
    plan = result.scalars().all()
    
    if plan:
        log_info(f"Found existing assessment plan with name '{name}' and type '{assessment_type}'", 
                module="crud.assessmentPlan")
    
    return plan

async def create_assessment_plan(db: AsyncSession, plan: AssessmentPlanCreate) -> AssessmentPlan:
    """
    创建新的评估计划

    Args:
        db: 数据库会话
        plan: 评估计划创建模型

    Returns:
        创建的评估计划对象
    """
    # 从Pydantic模型创建ORM实例
    db_plan =  AssessmentPlan(**plan.model_dump())
    
    # 添加到数据库
    db.add(db_plan)
    await db.commit()
    await db.refresh(db_plan)
    
    log_info(f"Created new assessment plan with ID {db_plan.id}", module="crud.assessmentPlan")
    return db_plan

async def update_assessment_plan(
    db: AsyncSession, 
    plan_id: UUID, 
    plan_update: AssessmentPlanUpdate
) -> Optional[AssessmentPlan]:
    """
    更新现有评估计划

    Args:
        db: 数据库会话
        plan_id: 要更新的评估计划ID
        plan_update: 包含更新数据的模型

    Returns:
        更新后的评估计划对象，如未找到则返回None
    """
    # 获取现有评估计划
    db_plan = await get_assessment_plan_by_id(db, plan_id)
    if db_plan is None:
        log_info(f"Update failed: No assessment plan found with ID {plan_id}", module="crud.assessmentPlan")
        return None
    
    # 更新非空字段
    update_data = plan_update.model_dump(exclude_unset=True)
    for key, value in update_data.items():
        if value is not None:  # 只更新非None值
            setattr(db_plan, key, value)
    
    await db.commit()
    await db.refresh(db_plan)
    
    log_info(f"Updated assessment plan with ID {plan_id}", module="crud.assessmentPlan")
    return db_plan

async def delete_assessment_plan(db: AsyncSession, plan_id: UUID) -> bool:
    """
    删除评估计划

    Args:
        db: 数据库会话
        plan_id: 要删除的评估计划ID

    Returns:
        删除成功返回True，如未找到则返回False
    """
    db_plan = await get_assessment_plan_by_id(db, plan_id)
    if db_plan is None:
        log_info(f"Delete failed: No assessment plan found with ID {plan_id}", module="crud.assessmentPlan")
        return False
    
    # 使用 ORM 的级联删除功能，而不是直接执行 SQL DELETE 语句
    # 这样会触发在 AssessmentPlan 模型中定义的级联删除关系
    await db.delete(db_plan)
    await db.commit()
    
    log_info(f"Deleted assessment plan with ID {plan_id}", module="crud.assessmentPlan")
    return True
